package com.exd.kafka.clickhouse;

import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

public class JdbcUtil {
	
    private static String url = "jdbc:clickhouse://10.82.66.246:8123/default";
    private static String user = "default";
    private static String password = "Exd@2023";
	
	private static Logger logger = LoggerFactory.getLogger(JdbcUtil.class);
	
	static{
	    try {
	    	Class.forName("ru.yandex.clickhouse.ClickHouseDriver");	
	    } catch (Exception exception) {
	    	exception.printStackTrace();
	    }
	}
	
	/**
	 * 描述: 查询数据列表
	 * 创建人: 赵兴炎
	 * 日期: 2023年09月25日
	 */
	public static List<Map<String, Object>> queryData(String querySql){
		logger.info("sql start--"+new Date().getTime()+"--"+querySql);
	    List<Map<String, Object>> resultList = new ArrayList<>();
	    Statement statement = null;
	    ResultSet resultSet = null;
	    try {
	        // 创建statement
	        statement = DriverManager.getConnection(url, user, password).createStatement();
	        // 执行查询语句
	        resultSet = statement.executeQuery(querySql);
	        logger.info("sql end--"+new Date().getTime()+"--"+querySql);
	        // 构建结果返回
	        buildMapByRs(resultList, resultSet);
	    } catch (Exception exception) {
	    	exception.printStackTrace();
	    } finally {
	        // 关闭resultSet
	        closeResultSet(resultSet);
	        // 关闭statement
	        closeStatement(statement);
	    }
        return resultList;
	}
	/**
	 * 描述: 分页查询数据
	 * 创建人: 赵兴炎
	 * 日期: 2023年09月25日
	 */
	public static List<Map<String, Object>> queryDataPageList(String querySql, Integer page, Integer size){
	    List<Map<String, Object>> resultList = new ArrayList<>();
	    Statement statement = null;
	    ResultSet resultSet = null;
	    try {
	        // 创建statement
	        statement = DriverManager.getConnection(url, user, password).createStatement();
	        // 格式化SQL
	        int offset = (page - 1 ) * size;
	        String execSql = String.format(querySql, offset, size);
	        // 执行查询语句
	        resultSet = statement.executeQuery(execSql);
	        // 构建结果返回
	        buildMapByRs(resultList, resultSet);
	    } catch (Exception exception) {
	    	exception.printStackTrace();
	    } finally {
	        // 关闭resultSet
	        closeResultSet(resultSet);
	        // 关闭statement
	        closeStatement(statement);
	    }
	    return resultList;
	}
	
	/**
	 * 描述: 分页查询数据
	 * 创建人: 赵兴炎
	 * 日期: 2023年09月25日
	 */
	public static Long queryDataPageCount(String querySql){
		Long total =0L;
	    Statement statement = null;
	    ResultSet resultSet = null;
	    try {
	        // 创建statement
	        statement = DriverManager.getConnection(url, user, password).createStatement();
	        // 执行查询语句
	        resultSet = statement.executeQuery(querySql);
	        while (resultSet.next()) {
	        	total = resultSet.getLong(1);
	        }
	        return total;
	    } catch (Exception exception) {
	    	return total;
	    } finally {
	        // 关闭resultSet
	        closeResultSet(resultSet);
	        // 关闭statement
	        closeStatement(statement);
	    }
	}
	
	/**
	 * 描述: 封装数据
	 * 创建人: 赵兴炎
	 * 日期: 2023年09月25日
	 */
	private static void buildMapByRs(List<Map<String, Object>> resultList, ResultSet resultSet) throws Exception {
		// 获取元数据
	    ResultSetMetaData metaData = resultSet.getMetaData();
	    while (resultSet.next()) {
	        // 获取列数
	        int columnCount = metaData.getColumnCount();
	        Map<String, Object> map = new HashMap<>();
	        for (int i = 0; i < columnCount; i++) {
	            String columnName = metaData.getColumnName(i + 1);
	            Object object = resultSet.getObject(columnName);
	            if(object == null) object = 0;
	            String keyValue = null;
	            String[] split = columnName.split("\\.");
	            if(split!=null && split.length > 0 && split.length == 2) {
	            	keyValue = split[1];
	            }else {
	            	keyValue = columnName;
	            }
	            if ("\\N".equalsIgnoreCase(String.valueOf(object))) {
	                map.put(keyValue, 0);
	            } else {
	                map.put(keyValue, object);
	            }
	        }
	         resultList.add(map);
	    }
	}

	/**
	 * 描述: 关闭Statement
	 * 创建人: 赵兴炎
	 * 日期: 2023年09月25日
	 */
	private static void closeStatement(Statement statement){
	    if (statement != null){
	        try {
	            statement.close();
	        } catch (SQLException e) {
	            e.printStackTrace();
	        }
	    }
	}

	/**
	 * 描述: 关闭ResultSet
	 * 创建人: 赵兴炎
	 * 日期: 2023年09月25日
	 */
	private static void closeResultSet(ResultSet resultSet){
	    if (resultSet != null){
	        try {
	            resultSet.close();
	        } catch (SQLException e) {
	            e.printStackTrace();
	        }
	    }
	}
	
	public static void main(String[] args) {
		String querySql ="SELECT toString(min(collect_time)) AS minTime from data_default_all";
		List<Map<String, Object>> queryData = queryData(querySql);
		Object object = queryData.get(0).get("minTime");
		System.out.println(object);
		
	//		String sql1="SELECT vin,collect_time FROM data_default_all  limit 2";
	//		List<Map<String, Object>> queryData = queryData(sql1);
	//		System.out.println(JsonTool.ObjectToJson(queryData));
		
//		String sql2="SELECT \r\n"
//				+ "    vin,\r\n"
//				+ "    toUInt32(toDateTime(collect_time)) as collect_time_long,\r\n"
//				+ "    toDateTime(collect_time,3) as collect_time_str \r\n"
//				+ "FROM \r\n"
//				+ "    data_default_all\r\n"
//				+ "WHERE \r\n"
//				+ "    collect_time_long >= 1709671081 AND collect_time_long <= 1709671081 and vin='LDPTEST62NE008888'\r\n"
//				+ "limit 30";
//		List<Map<String, Object>> queryData2 = queryData(sql2);
//		System.out.println(JsonTool.ObjectToJson(queryData2));
	}
}
